USE GESTION2
go

IF OBJECT_ID('dbo.WASP_D0005_CARGA_TABLA_TRABAJADORES') IS NOT NULL 
   BEGIN
      DROP PROCEDURE dbo.WASP_D0005_CARGA_TABLA_TRABAJADORES 
      IF OBJECT_ID('dbo.WASP_D0005_CARGA_TABLA_TRABAJADORES') 
      IS
      NOT NULL 
         PRINT 
         '<<< FAILED DROPPING PROCEDURE dbo.WASP_D0005_CARGA_TABLA_TRABAJADORES >>>'
      ELSE
         PRINT 
         '<<< DROPPED PROCEDURE dbo.WASP_D0005_CARGA_TABLA_TRABAJADORES >>>' 
   END
go

SET ANSI_NULLS ON
go

SET QUOTED_IDENTIFIER ON
go

CREATE PROCEDURE WASP_D0005_CARGA_TABLA_TRABAJADORES
AS
   SELECT A.CTNRO,
      B.Z07369CT1,
      A.CTCCLI,
      --CAST(B.Z07369CT1 AS DECIMAL(9,0)) AS CLVEMPRESA,
      CAST(RTRIM(CAST(J.Z07327COR AS CHAR)) + REPLICATE('0', 2 - LEN(CAST(J.Z07327MAT
      AS CHAR))) + CAST(J.Z07327MAT AS CHAR) AS DECIMAL(9, 0)) AS CLVEMPRESA,
      ESTADOTRAB = 
      CASE WHEN B.Z07369EST = 'S' THEN 
        1 
      ELSE
        9 
      END,  
      C.PENDOC AS RUTEMPRESA,
      D.PENDOC AS RUTTRABAJA,
      (RTRIM(E.PFNOM1) + ' ' + RTRIM(E.PFNOM2) + ' ' + RTRIM(E.PFAPE1) + ' ' + RTRIM(PFAPE2)) AS NOMTRABAJA,
      G.Z07325AGE AS CODAGENCIA,
      E.PFFNAC AS FNACITRABA,
      SEXOTRABAJ = 
      CASE WHEN E.PFCANT = 'M' THEN 
        0 
      ELSE
        1 
      END,
      DOCALLP + CONVERT(VARCHAR, DONROP)AS DIRTRABAJA,
      H.Z07310COM AS COMUNATRAB 
   INTO DBO.CAPPF000_TEMP 
   FROM Z07369 B (NOLOCK) LEFT JOIN Z07327 J (NOLOCK) ON B.Z07369CT1 = J.Z07327CTA
   , FSD008 A (NOLOCK) LEFT JOIN Z07326 H (NOLOCK) ON A.CTNRO = SUBSTRING( 
   Z07326NDO, 1, LEN(Z07326NDO) - 1) LEFT JOIN FSD005 I (NOLOCK) ON A.CTNRO = 
   SUBSTRING(I.PENDOC, 1, LEN(I.PENDOC) - 1), 
   --	FSD008 A (NOLOCK),
   --	Z07369 B (NOLOCK),
   FSR008 C (NOLOCK), FSR008 D (NOLOCK), FSD002 E (NOLOCK), Z07325 G (NOLOCK ) 
   --	Z07326 H (NOLOCK),
   --	FSD005 I (NOLOCK)
   --	Z07327 J (NOLOCK)
   WHERE A.CTCCLI IN (3, 4, 5) 
   AND A.CTNRO = B.Z07369CT2 
   AND B.Z07369CT1 = C.CTNRO 
   AND 
   --	B.Z07369CT1 *= J.Z07327CTA AND
   --	B.Z07369EST = 'S'AND
   J.Z07327EST = 'AC' 
   AND A.CTNRO = D.CTNRO 
   AND d.Cttfir = 'T' 
   AND A.CTNRO = SUBSTRING(E.PFNDOC, 1, LEN(E.PFNDOC) - 1) 
   AND E.PFNDOC <> '' 
   AND B.Z07369CT2 = G.Z07325CTA 
   AND 
   --	A.CTNRO *= SUBSTRING(Z07326NDO,1,LEN(Z07326NDO)-1) AND Z07326NDO<>'' AND
   Z07326NDO <> '' 
   AND 
   --	A.CTNRO *= SUBSTRING(I.PENDOC,1,LEN(I.PENDOC)-1) AND I.PENDOC<>''
   I.PENDOC <> '' 
   AND I.PENDOC <> '*********8' 
   AND E.PFNDOC <> '*********8' 
   AND C.PENDOC <> '*********8' 
   AND D.PENDOC <> '*********8' 
   AND Z07326NDO <> '*********8'
go



SET ANSI_NULLS OFF
go

SET QUOTED_IDENTIFIER OFF
go

IF OBJECT_ID('dbo.WASP_D0005_CARGA_TABLA_TRABAJADORES') IS NOT NULL 
   PRINT '<<< CREATED PROCEDURE dbo.WASP_D0005_CARGA_TABLA_TRABAJADORES >>>' 
ELSE
   PRINT 
   '<<< FAILED CREATING PROCEDURE dbo.WASP_D0005_CARGA_TABLA_TRABAJADORES >>>'
go
